﻿import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.*;
import java.sql.*;
import java.io.*;
import java.awt.*;
import javax.swing.*;

public class Datenbank {
	public String slon_lat = null;
	private static SimpleDateFormat formater = new SimpleDateFormat();
	private int MAX_BUTTON = 9;
	private String strUsername = "";
	private String strPassword = "";
	private String strDriver = "com.mysql.jdbc.Driver";
	private String strURL = "";
	private Connection connection;

	
	public static void main(String[] args) {
		ComListener.debug("DATENBANK", "DEBUG>> Datenbank V1.0 __ Start um " + formater.format(Calendar.getInstance().getTime()));
	}
	
	public Datenbank() {
		try {
			BufferedReader in = new BufferedReader(new FileReader("db.ini"));
			String str;
			while ((str = in.readLine()) != null) {
				if (str.startsWith("USER=")) {
					strUsername=str.substring(5, str.length());
					ComListener.debug("DATENBANK", "DEBUG>> DB-User: " + strUsername);
				}
				if (str.startsWith("PASS=")) {
					strPassword=str.substring(5, str.length());
					ComListener.debug("DATENBANK", "DEBUG>> DB-Pass: XXX ");
				}
				if (str.startsWith("URL =")) {
					strURL=str.substring(5, str.length());
					ComListener.debug("DATENBANK", "DEBUG>> DB-URL: " + strURL);
				}
			}
			in.close();
		} catch (IOException err) {
			ComListener.debug("DATENBANK", "ERROR>> IOException: "  + err);
		}	
	}

	
	/**
	* DB-Connection herstellen
	*/
	public boolean dbOpen() {
		try {
			Class.forName(strDriver);
			connection = DriverManager.getConnection(strURL, strUsername, strPassword);
		} catch (Exception err) {
			ComListener.debug("DATENBANK", "ERROR>> Error Connecting with User:" + strUsername + " and Password:" + strPassword + " --- " + err);
			return false;
		}
		return true;
	}

	
	/**
	* DB-Connection schliessen
	*/
	public void dbClose() {
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e) {
			}
		}
	}

	
	/**
	* Insert in Nachrichten RAW
	*/
	public void dbInsertRaw(String str) {
		String strInsert = "insert into nachrichten_raw (text) values (?)";
		
		if (dbOpen()==false) {
			return;
		}
		try {
			ComListener.debug("DATENBANK", "DEBUG>> [INSERT]: " + formater.format(Calendar.getInstance().getTime()) +  " --- " + str);
			PreparedStatement ps = connection.prepareStatement(strInsert);
			ps.setString(1, str);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException err) {
			ComListener.debug("DATENBANK", "ERROR>> " + err.toString());
		}
		dbClose();
	}

	/**
	* Abfrage der Orte
	*/
	public String dbQueryOrt(String str, String strOrt) {
		int x=0;
		String strLangEinsatz = "";
		String sPk1 = "-1", sVPk1 = "";
		String s1 = "", s2 = "";
		
		if (dbOpen()==false) {
			return "";
		}
		ComListener.debug("DATENBANK", "DEBUG2>> " + strOrt);
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("select pk, vpk from orte_neu where concat(plz,' ',ort) = '" + strOrt + "'" + " or ort = '" + strOrt + "'");
			while (rs.next()) {
				sPk1 = rs.getString(1);
				sVPk1 = rs.getString(2);
				ComListener.debug("DATENBANK", "DEBUG2>> " + sPk1 + " VPK: " + sVPk1);
			}
			rs.close();
			stmt.close();
			
//			if (!sPk1 != null) sPk1 = "-1";
			
			stmt = connection.createStatement();
			rs = stmt.executeQuery("select orte.plz, orte.stadt, lat_lon from orte_neu orte where orte.pk = " + sPk1 + "");
			while (rs.next()) {
				s1 = rs.getString(1);
				s2 = rs.getString(2);
				slon_lat = rs.getString(3);
			}
		} catch (SQLException err) {
			ComListener.debug("DATENBANK", "ERROR2>> " + err.toString());
		}
		dbClose();
		return s1 + " " + s2;
	}
				
	/**
	* Abfrage der Fahrzeuge
	*/
	public String dbQueryFahrzeuge(String str, String strOrt, JButton jb[]) {
		int x=0;
		String strLangEinsatz = "";
		String sPk1 = "", sVPk1 = "";
		
		if (dbOpen()==false) {
			return "";
		}
		try {
			ComListener.debug("DATENBANK", "DEBUG>> " + strOrt);
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("select pk, vpk from orte_neu where concat(plz,' ',ort) = '" + strOrt + "'" +
					"or ort = '" + strOrt + "'");	//BAB_WL oder 21534 BAD BEvens
			while (rs.next()) {
				sPk1 = rs.getString(1);
				sVPk1 = rs.getString(2);
				ComListener.debug("DATENBANK", "DEBUG>> " + sPk1 + " VPK: " + sVPk1);
			}
			rs.close();
			stmt.close();
			
			if (!sVPk1.equals("0")) {
				sPk1 = sVPk1;
			}
			
			
			stmt = connection.createStatement();
			rs = stmt.executeQuery("select fahrzeuge.fzg, stichworte.bezeichnung, orte.ort from stichworte, fahrzeuge, aao_neu aao, orte_neu orte where fahrzeuge.pk = aao.f_pk and stichworte.pk = aao.s_pk "
                   + "and orte.pk = aao.o_pk and orte.pk = '" + sPk1 + "'"
						       + "and stichworte.stichwort in (select IF(stichworte.verknuepfung is null, stichworte.stichwort, stichworte.verknuepfung) "
						       + "from stichworte where stichworte.stichwort = '" + str + "') group by 1 order by aao.folge asc");
			while (rs.next()) {
				String s1 = rs.getString(1);
				strLangEinsatz = rs.getString(2);
				jb[x].setFont(new Font("Arial", Font.BOLD, 70));
				jb[x].setBackground(Color.RED);
				jb[x++].setText(s1);
				ComListener.debug("DATENBANK", "DEBUG>> " + s1 + " Lang: " + strLangEinsatz);
			}
			rs.close();
			stmt.close();
			for (; x < MAX_BUTTON; x++) {
				jb[x].setText("");
				jb[x].setBackground(Color.LIGHT_GRAY);
			}
		} catch (SQLException err) {
			ComListener.debug("DATENBANK", "ERROR>> " + err.toString());
		}
		dbClose();
		return strLangEinsatz;
	}
	
	
	      /**
        * Abfrage der RIC
        */
        public boolean dbCheckRIC(String str) {
                String s1 = "dada";
                if (dbOpen()==false) {
                        return false;
                }
                try {
                                               ComListener.debug("DATENBANK", "DEBUG>> RIC start");
                        Statement stmt = connection.createStatement();
                        ResultSet rs = stmt.executeQuery("select value from config where name = 'comlistener_ric' and value = '" + str + "'");
                        while (rs.next()) {
                                s1 = rs.getString(1);
                                                               ComListener.debug("DATENBANK", "DEBUG>> RIC:" + s1);
                                               }
                        rs.close();
                        stmt.close();
                                               dbClose();
                        if (s1.equals("dada")) {
                                                               ComListener.debug("DATENBANK", "DEBUG>> RIC return false");
                                return false;
                        }
                } catch (SQLException err) {
                        ComListener.debug("DATENBANK", "ERROR>> " + err.toString());
                }
                               ComListener.debug("DATENBANK", "DEBUG>> RIC return true");
                return true;
        }

	
	/**
	* Abfrage von Konfigdaten
	*/
	public String dbQueryConfig(String str) {
		String s1 = null;
		
		if (dbOpen()==false) {
			return "";
		}
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("select value from config where name = '" + str + "'");
			while (rs.next()) {
				s1 = rs.getString(1);
				if (str.equals("mail_server_pw")) {
					ComListener.debug("DATENBANK", "DEBUG>> Config: " + str + " --> XXX");
				} else {
					ComListener.debug("DATENBANK", "DEBUG>> Config: " + str + " --> " + s1);
				}
			}
			rs.close();
			stmt.close();
		} catch (SQLException err) {
			ComListener.debug("DATENBANK", "ERROR>> " + err.toString());
			return "";
		}
		dbClose();
		return s1;
	}
	
	/**
	* Abfrage von EMaildaten
	*/
	public Vector<String> dbQueryEMail() {
		Vector<String> v = new Vector<String>();
		String s1 = "";
		
		if (dbOpen()==false) {
			return v;
		}
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("select value from config where name = 'mail_recipient'");
			while (rs.next()) {
				s1 = rs.getString(1);
				v.addElement(s1);
				ComListener.debug("DATENBANK", "DEBUG>> EMail: " + s1);
			}
			rs.close();
			stmt.close();
		} catch (SQLException err) {
			ComListener.debug("DATENBANK", "ERROR>> " + err.toString());
			return v;
		}
		dbClose();
		return v;
	}
}
